package com.apestech.framework.metadata;

import com.apestech.framework.db.SqlResultSet;
import com.apestech.framework.db.callback.QueryCallBackHandler;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class OracleDBMD {

    private Connection cn=null;

    public OracleDBMD(Connection cn){
        this.cn=cn;
    }

    public void getTables(String schem, String tableName, QueryCallBackHandler queryCallBackHandler) throws Exception {
        String sql="SELECT O.OWNER AS TABLE_SCHEM,O.OBJECT_NAME AS TABLE_NAME,C.COMMENTS AS REMARKS\n" +
                "  FROM ALL_OBJECTS O, ALL_TAB_COMMENTS C\n" +
                "  WHERE O.OWNER LIKE ?\n" +
                "    AND O.OBJECT_NAME LIKE ?\n" +
                "    AND O.OBJECT_TYPE IN ('TABLE', 'VIEW')\n" +
                "    AND O.OWNER = C.OWNER (+)\n" +
                "    AND O.OBJECT_NAME = C.TABLE_NAME (+)\n" +
                "  ORDER BY TABLE_TYPE, TABLE_SCHEM, TABLE_NAME";
        execQry(sql, new DBMDExecInter(){
            @Override
            public void setPsmt(PreparedStatement psmt) throws Exception {
                if(schem==null){
                    psmt.setString(1, "%");
                } else {
                    psmt.setString(1, schem);
                }
                if(tableName==null){
                    psmt.setString(2, "%");
                } else {
                    psmt.setString(2, tableName);
                }
            }
        }, queryCallBackHandler);
    }

    public void getColumns(String schem, String tableName, String columnName, QueryCallBackHandler queryCallBackHandler) throws Exception {
        String sql="SELECT T.OWNER AS TABLE_SCHEM,\n" +
                "       T.TABLE_NAME AS TABLE_NAME,\n" +
                "       T.COLUMN_NAME AS COLUMN_NAME,\n" +
                "       DECODE (T.DATA_TYPE, 'CHAR', 1, 'VARCHAR2', 12, 'NUMBER', 3,\n" +
                "               'LONG', -1, 'DATE', 93, 'RAW', -3, 'LONG RAW', -4,  \n" +
                "               'BLOB', 2004, 'CLOB', 2005, 'BFILE', -13, 'FLOAT', 6, \n" +
                "               'TIMESTAMP(6)', 93, 'TIMESTAMP(6) WITH TIME ZONE', -101, \n" +
                "               'TIMESTAMP(6) WITH LOCAL TIME ZONE', -102, \n" +
                "               'INTERVAL YEAR(2) TO MONTH', -103, \n" +
                "               'INTERVAL DAY(2) TO SECOND(6)', -104, \n" +
                "               'BINARY_FLOAT', 100, 'BINARY_DOUBLE', 101, \n" +
                "               'XMLTYPE', 2007, \n" +
                "               1111)\n" +
                "              AS DATA_TYPE,\n" +
                "       T.DATA_TYPE AS TYPE_NAME,\n" +
                "       DECODE (T.DATA_PRECISION, NULL,          DECODE (T.DATA_TYPE, 'CHAR', T.CHAR_LENGTH,                   'VARCHAR', T.CHAR_LENGTH,                   'VARCHAR2', T.CHAR_LENGTH,                   'NVARCHAR2', T.CHAR_LENGTH,                   'NCHAR', T.CHAR_LENGTH,           T.DATA_LENGTH),         T.DATA_PRECISION)\n" +
                "              AS COLUMN_SIZE,\n" +
                "       0 AS BUFFER_LENGTH,\n" +
                "       T.DATA_SCALE AS DECIMAL_DIGITS,\n" +
                "       10 AS NUM_PREC_RADIX,\n" +
                "       DECODE (T.NULLABLE, 'N', 0, 1) AS NULLABLE,\n" +
                "       C.COMMENTS AS REMARKS,\n" +
                "       T.DATA_DEFAULT AS COLUMN_DEF,\n" +
                "       0 AS SQL_DATA_TYPE,\n" +
                "       0 AS SQL_DATETIME_SUB,\n" +
                "       T.DATA_LENGTH AS CHAR_OCTET_LENGTH,\n" +
                "       T.COLUMN_ID AS ORDINAL_POSITION,\n" +
                "       DECODE (T.NULLABLE, 'N', 'NO', 'YES') AS IS_NULLABLE\n" +
                "FROM ALL_TAB_COLUMNS T, ALL_COL_COMMENTS C\n" +
                "WHERE T.OWNER LIKE ?\n" +
                "  AND T.TABLE_NAME =?\n" +
                "  AND T.COLUMN_NAME LIKE ?\n" +
                "  AND T.OWNER = C.OWNER (+)\n" +
                "  AND T.TABLE_NAME = C.TABLE_NAME (+)\n" +
                "  AND T.COLUMN_NAME = C.COLUMN_NAME (+)\n" +
                "ORDER BY TABLE_SCHEM, TABLE_NAME, ORDINAL_POSITION";
        execQry(sql, new DBMDExecInter(){
            @Override
            public void setPsmt(PreparedStatement psmt) throws Exception {
                if(schem==null){
                    psmt.setString(1, "%");
                } else {
                    psmt.setString(1, schem);
                }
                psmt.setString(2, tableName);
                if(columnName==null){
                    psmt.setString(3, "%");
                } else {
                    psmt.setString(3, columnName);
                }
            }
        }, queryCallBackHandler);
    }

    public void getPrimaryKeys(String schem, String tableName, QueryCallBackHandler queryCallBackHandler) throws Exception {
        String sql="SELECT C.OWNER AS TABLE_SCHEM, C.TABLE_NAME, C.COLUMN_NAME, C.POSITION AS KEY_SEQ, C.CONSTRAINT_NAME AS PK_NAME\n" +
                "  FROM ALL_CONS_COLUMNS C, ALL_CONSTRAINTS K\n" +
                " WHERE K.CONSTRAINT_TYPE = 'P'\n" +
                "   AND K.TABLE_NAME = ?\n" +
                "   AND K.OWNER LIKE ?\n" +
                "   AND K.CONSTRAINT_NAME = C.CONSTRAINT_NAME\n" +
                "   AND K.TABLE_NAME = C.TABLE_NAME\n" +
                "   AND K.OWNER = C.OWNER\n" +
                " ORDER BY COLUMN_NAME";
        execQry(sql, new DBMDExecInter(){
            @Override
            public void setPsmt(PreparedStatement psmt) throws Exception {
                psmt.setString(1, tableName);
                if(schem==null){
                    psmt.setString(2, "%");
                } else {
                    psmt.setString(2, schem);
                }
            }
        }, queryCallBackHandler);
    }

    public void getImportedKey(String schem, String tableName, QueryCallBackHandler queryCallBackHandler) throws Exception {
        String sql="SELECT F.OWNER AS FKTABLE_SCHEM, F.TABLE_NAME AS FKTABLE_NAME, F.CONSTRAINT_NAME AS FK_NAME,\n" +
                "       (SELECT TO_CHAR(WM_CONCAT(Z.COLUMN_NAME)) FROM ALL_CONS_COLUMNS Z WHERE Z.OWNER = F.OWNER AND Z.CONSTRAINT_NAME = F.CONSTRAINT_NAME AND Z.TABLE_NAME = F.TABLE_NAME) AS FKCOLUMN_NAME,\n" +
                "       (SELECT Z.POSITION FROM ALL_CONS_COLUMNS Z WHERE Z.OWNER = F.OWNER AND Z.CONSTRAINT_NAME = F.CONSTRAINT_NAME AND Z.TABLE_NAME = F.TABLE_NAME AND ROWNUM=1) AS KEY_SEQ,\n" +
                "       P.TABLE_NAME AS PKTABLE_NAME, P.CONSTRAINT_NAME AS PK_NAME,\n" +
                "       (SELECT TO_CHAR(WM_CONCAT(Z.COLUMN_NAME)) FROM ALL_CONS_COLUMNS Z WHERE Z.OWNER = P.OWNER AND Z.CONSTRAINT_NAME = P.CONSTRAINT_NAME AND Z.TABLE_NAME = P.TABLE_NAME) AS PKCOLUMN_NAME\n" +
                "FROM ALL_CONSTRAINTS P, ALL_CONSTRAINTS F\n" +
                "WHERE 1 = 1\n" +
                "  AND F.TABLE_NAME = ?\n" +
                "  AND F.OWNER = ?\n" +
                "  AND F.CONSTRAINT_TYPE = 'R'\n" +
                "  AND P.OWNER = F.R_OWNER  AND P.CONSTRAINT_NAME = F.R_CONSTRAINT_NAME  \n" +
                "  AND P.CONSTRAINT_TYPE = 'P'\n" +
                "ORDER BY FKTABLE_SCHEM, PKTABLE_NAME, KEY_SEQ";
        execQry(sql, new DBMDExecInter(){
            @Override
            public void setPsmt(PreparedStatement psmt) throws Exception {
                psmt.setString(1, tableName);
                if(schem==null){
                    psmt.setString(2, "%");
                } else {
                    psmt.setString(2, schem);
                }
            }
        }, queryCallBackHandler);
    }

    public void getIndexInfo(String schem, String tableName, QueryCallBackHandler queryCallBackHandler) throws Exception {
        String sql="SELECT OWNER AS TABLE_SCHEM, TABLE_NAME, NULL AS INDEX_NAME, 0 AS TYPE, 0 AS ORDINAL_POSITION, NULL AS COLUMN_NAME\n" +
                "FROM ALL_TABLES\n" +
                "WHERE TABLE_NAME = ? AND OWNER = ? \n" +
                "UNION\n" +
                "SELECT I.OWNER AS TABLE_SCHEM, I.TABLE_NAME, I.INDEX_NAME, 1 AS TYPE, C.COLUMN_POSITION AS ORDINAL_POSITION, C.COLUMN_NAME\n" +
                "FROM ALL_INDEXES I, ALL_IND_COLUMNS C\n" +
                "WHERE I.TABLE_NAME = ? AND I.OWNER = ? AND I.UNIQUENESS = 'UNIQUE'\n" +
                "  AND I.INDEX_NAME = C.INDEX_NAME AND I.TABLE_OWNER = C.TABLE_OWNER AND I.TABLE_NAME = C.TABLE_NAME\n" +
                "  AND I.OWNER = C.INDEX_OWNER\n" +
                "ORDER BY TYPE, INDEX_NAME, ORDINAL_POSITION\n";
        execQry(sql, new DBMDExecInter(){
            @Override
            public void setPsmt(PreparedStatement psmt) throws Exception {
                psmt.setString(1, tableName);
                psmt.setString(3, tableName);
                if(schem==null){
                    psmt.setString(2, "%");
                    psmt.setString(4, "%");
                } else {
                    psmt.setString(2, schem);
                    psmt.setString(4, schem);
                }
            }
        }, queryCallBackHandler);
    }

    private void execQry(String sql, DBMDExecInter dbmdExecInter, QueryCallBackHandler queryCallBackHandler) throws Exception {
        SqlResultSet rs=null;
        PreparedStatement psmt=null;
        try {
            psmt = cn.prepareStatement(sql);
            if(dbmdExecInter!=null){
                dbmdExecInter.setPsmt(psmt);
            }
            rs=new SqlResultSet(psmt.executeQuery());
            queryCallBackHandler.getData(rs, null);
        } catch (SQLException e){
            throw new Exception(e);
        } finally {
            if(rs!=null) rs.close();
            if(psmt!=null) psmt.close();
        }
    }

}
